﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyKhachSan.DataAccess;
using QuanLyKhachSan.BusinessObject;
namespace QuanLyKhachSan.BusinessLogic
{
    class KhachHangBL
    {
        ConnectData connData = new ConnectData();
        
        //Tim kiem
        public DataTable TimKiemKhachHang(string timKiem)
        {
            string sql = "select * from KhachHang where TenKhachHang like '%" + timKiem + "%' or MaKhachHang like '%" + timKiem + "%'";
            return connData.GetDataTable(sql);
        }

        //lấy danh sách khách hàng
        public DataTable LayDSKhachHang() {
            string sql = "LayDSKhachHang";
            return connData.GetDataTable(sql);
        }

        //lấy danh sách giới tinh
        public DataTable LayDSGioiTinh() {
            string sql = "select distinct GioiTinh,"
               + "NameGT = case GioiTinh when 0 then N'Nam'"
               + "when 1 then N'Nữ' end from NhanVien";
            return connData.GetDataTable(sql);
        }

        //thêm mới khách hàng
        public void ThemKH(KhachHangBO kh) {
                //string sql = string.Format("INSERT INTO KhachHang (MaKhachHang, TenKhachHang, NgaySinh, GioiTinh, DiaChi, SoDienThoai, SoChungMinhThu) "
                //     + "VALUES ('{0}','{1}','{2}',{3},'{4}','{5}','{6}')", kh.MaKhachHang, kh.TenKhachHang, kh.NgaySinh.ToString("MM-dd-yyyy"), kh.GioiTinh, kh.DiaChi, kh.SoDienThoai, kh.SoChungMinhThu);
                //if (connData.ExeCuteQuery(sql))
                //{
                //    MessageBox.Show("Thêm mới khách hàng thành công!","Thông Báo",MessageBoxButtons.OK,MessageBoxIcon.Information);
                //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "ThemMoiKhachHang";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
            objCmd.Parameters.Add("@GioiTinh", SqlDbType.Int);
            objCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoChungMinhThu", SqlDbType.NVarChar, 30);


            objCmd.Parameters["@MaKhachHang"].Value = kh.MaKhachHang;
            objCmd.Parameters["@TenKhachHang"].Value = kh.TenKhachHang;
            objCmd.Parameters["@NgaySinh"].Value = kh.NgaySinh;
            objCmd.Parameters["@GioiTinh"].Value = kh.GioiTinh;
            objCmd.Parameters["@DiaChi"].Value = kh.DiaChi;
            objCmd.Parameters["@SoDienThoai"].Value = kh.SoDienThoai;
            objCmd.Parameters["@SoChungMinhThu"].Value = kh.SoChungMinhThu;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //
        public string NextID()
        {
            return Utilities.NextID(connData.GetLastID("KhachHang", "MaKhachHang"), "KH");
        }

        public bool CheckExists(string makh)
        {
            if (connData.CheckExistValue("KhachHang", "MaKhachHang", makh))
            {
                return true;
            } return false;
        }

        public void SuaKH(KhachHangBO kh)
        {
            //string sql = string.Format("UPDATE KhachHang SET TenKhachHang = '{0}', NgaySinh = '{1}', "
            //                + "GioiTinh = {2}, DiaChi = '{3}', SoDienThoai = '{4}', SoChungMinhThu = '{5}' WHERE MaKhachHang = '{6}' ",
            //                         kh.TenKhachHang, kh.NgaySinh.ToString("MM-dd-yyyy"), kh.GioiTinh, kh.DiaChi, kh.SoDienThoai, kh.SoChungMinhThu, kh.MaKhachHang);
            //if (connData.ExeCuteQuery(sql))
            //{
            //    MessageBox.Show("Sửa khách hàng thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "SuaKhachHang";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime);
            objCmd.Parameters.Add("@GioiTinh", SqlDbType.Int);
            objCmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoChungMinhThu", SqlDbType.NVarChar, 30);


            objCmd.Parameters["@MaKhachHang"].Value = kh.MaKhachHang;
            objCmd.Parameters["@TenKhachHang"].Value = kh.TenKhachHang;
            objCmd.Parameters["@NgaySinh"].Value = kh.NgaySinh;
            objCmd.Parameters["@GioiTinh"].Value = kh.GioiTinh;
            objCmd.Parameters["@DiaChi"].Value = kh.DiaChi;
            objCmd.Parameters["@SoDienThoai"].Value = kh.SoDienThoai;
            objCmd.Parameters["@SoChungMinhThu"].Value = kh.SoChungMinhThu;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //xóa
        public void Delete(string kh)
        {
            string sql = "Delete from KhachHang where MaKhachHang='" + kh + "'";
            connData.ExeCuteQuery(sql);
        }

        //Dem tong phong
        public static int DemKhach()
        {
            ConnectData connData1 = new ConnectData();
            SqlConnection conn = connData1.Connect1();
            conn.Open();
            SqlCommand objCmd = new SqlCommand("select * from KhachHang", conn);
            SqlDataReader dr = objCmd.ExecuteReader();
            int count = 0;
            while (dr.Read())
            {
                count++;
            }
            conn.Close();
            return count;
        }
    }
}
